<?php

namespace App\Services\Statistics;

use Carbon\Carbon;
use App\Models\User;
use App\Models\Order;
use App\Models\Comment;
use App\Models\Product;
use App\Models\ProductSku;
use App\Models\VipPurchase;
use App\Models\VipCard;
use App\Models\GoldRecord;
use App\Models\Apple;
use App\Models\Balance;
use App\Models\Reward;
use App\Models\OrderRefund;
use App\Models\Withdraw;
use Illuminate\Support\Facades\DB;
use Encore\Admin\Facades\Admin;
use App\Models\Statistic;
class SearchCount
{


	/*---------- gold charges ------------------------------------------------------------------------------------------*/
	
	/** total_gold_charges_money_now */

	public function searchVipChargesMoney($result)
	{
		return VipPurchase::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where("paid_method","!=","card")
			->sum('total_amount');
	}

	public function searchVipCount($result)
	{
		return VipPurchase::query()
			->whereNotNull('paid_at')
			->count();
	}

	public function searchVipCountDay($result)
	{
		return VipPurchase::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->count();
	}



	public function searchVipChargesMoneyByLink($result)
	{
		$cards =  VipCard::query()
			->where('remark','buy_vip_cards')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');

		$notCards =  VipCard::query()
			->where('remark','system_recall')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');

		return ($cards + $notCards) * 9.9;
	}

	public function searchNewVipChargeMoney($result)
	{
		return VipPurchase::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where("is_new",true)
			->sum('total_amount');
	}

	public function searchOwnAppleProduce($result)
	{
		$time = str_replace('-', "", $result['start']);
		$res = Statistic::query()
			->where('title','own_tree_produce_apples_'.$time)
			->orderByDesc('id')
			->first();
		return $res ? $res->value : 0.00;
	}

	public function searchOneLevelTeamAppleProduce($result)
	{
		$time = str_replace('-', "", $result['start']);
		$res = Statistic::query()
			->where('title','1_tree_produce_apples_'.$time)
			->orderByDesc('id')
			->first();
		return $res ? $res->value : 0.00;
	}

	public function searchTwoLevelTeamAppleProduce($result)
	{
		$time = str_replace('-', "", $result['start']);
		$res = Statistic::query()
			->where('title','2_tree_produce_apples_'.$time)
			->orderByDesc('id')
			->first();
		return $res ? $res->value : 0.00;
	}


	public function searchAppleTreeProduce($result)
	{
		$time = str_replace('-', "", $result['start']);
		$res = Statistic::query()
			->where('title','garden_produce_apples_'.$time)
			->orderByDesc('id')
			->first();
		return $res ? $res->value : 0.00;
	}

	public function searchApplesExchangeBalances($result)
	{
		return Apple::query()
			->where('remark','apples_exchange_balances')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}
	

	public function searchApplesExchangeOrders($result)
	{
		return Apple::query()
			->where('remark','apple_exchange_order')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}
	/*---------- withdraw ------------------------------------------------------------------------------------------*/

	public function searchWithdrawActualMoney($result)
	{
		return Withdraw::query()
			->where('status', 'paid')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('actual_amount');
	}

	public function searchWithdrawAll($result)
	{
		return  Withdraw::query()
			->where('status', 'paid')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('target_amount');


	}


	public function searchWithdrawChargeMoney($result)
	{
		return Withdraw::query()
			->where('status', 'paid')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('charge_amount');
	}

	public function searchPendingWithdrawByAlipay($result)
	{
		return Withdraw::query()
			->where('status', 'pending')
			->where('method', 'alipay')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('actual_amount');
	}

	public function searchPendingWithdrawByBank($result)
	{
		return Withdraw::query()
			->where('status', 'pending')
			->where('method', 'mybank')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('actual_amount');
	}


	public function searchCommanderWages($result)
	{

		return Balance::query()
			->where('remark','company_transfer_balances')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where('company_job', 'commander')
			->sum('money');
	}

	
	public function searchCommissionerrWages($result)
	{
		
		return Balance::query()
			->where('remark','company_transfer_balances')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where('company_job', 'commissioner')
			->sum('money');
	}
	
	public function searchMajordomoWages($result)
	{
		
		return Balance::query()
			->where('remark','company_transfer_balances')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where('company_job', 'majordomo')
			->sum('money');
	}

	public function searchTraderWages($result)
	{
		
		return Balance::query()
			->where('remark','company_transfer_balances')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where('company_job', 'trader')
			->sum('money');
	}

		public function searchTechnologyWages($result)
	{
		
		return Balance::query()
			->where('remark','company_transfer_balances')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where('company_job', 'technology')
			->sum('money');
	}


	public function searchVideoWages($result)
	{
		
		return Balance::query()
			->where('remark','company_transfer_balances')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where('company_job', 'video')
			->sum('money');
	}

	public function searchWxPayOrder($result)
	{
		
		return Order::query()
			->where('paid_method','wxpay')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where('has_refund', false)
			->sum('total_amount');
	}

	public function searchAliPayOrder($result)
	{
		
		return Order::query()
			->where('paid_method','alipay')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where('has_refund', false)
			->sum('total_amount');
	}

	public function searchBalanceOrder($result)
	{
		
		return Order::query()
			->where('paid_method','balance')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where('has_refund', false)
			->sum('total_amount');
	}

	/*---------- gold beans --------------------------------------------------------------------------------------------*/

	public function searchGoldBeansFee($result)
	{
		$mockedUser = User::where('is_mock',1)->pluck('id')->toArray();
		$mockUserId = !empty($mockedUser) ? $mockedUser : []; 

		$query = GoldRecord::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->whereNotIn('user_id', $mockUserId);
		if($result['gold_round_id']){
			$query = $query->where('gold_round_id', $result['gold_round_id'])->sum('pay_gold_beans');
		}else{

			$query = $query->sum('pay_gold_beans');
		}
		return $query;	
	}


	public function searchGoldBeansFeeTotal($result)
	{
		$mockedUser = User::where('is_mock',1)->pluck('id')->toArray();
		$mockUserId = !empty($mockedUser) ? $mockedUser : []; 
		$query = GoldRecord::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->whereNotIn('user_id', $mockUserId)
			->sum('pay_gold_beans');
		
		return $query;	
	}
	/*---------- user --------------------------------------------------------------------------------------------*/

	public function searchUserBalances()
	{
		return User::query()
			->sum('balances');
	}

	public function searchUserSupportGoldCoins()
	{
		return GoldCoin::query()
			->where('remark','support_gold_coins')
			->sum('money');
	}


	public function searchUserGoldBeans()
	{
		return User::query()
			->sum('gold_beans');
	}

	public function searchUserXiaoerGoldBeans($result)
	{
		return GoldBean::query()
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where('remark','present_gold_beans')
			->sum('money');
	}

	public function searchUserChargeGoldBeans($result)
	{
		return GoldBean::query()
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where('remark','charge_gold_beans')
			->sum('money');
	}


	public function searchUserSupportGoldBeans()
	{
		return GoldBean::query()
			->where('remark','support_gold_beans')
			->sum('money');
	}

	public function searchUserTotalAmount()
	{
		return User::query()
			->count();
	}

	public function searchUserGoldLevelGreaterThanZeroTotalAmount()
	{
		return User::query()
			->where('is_gold_master' , 1)
			->count();
	}

	public function searchUserGrowth($result)
	{
		return User::query()
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->count();
	}

	/*---------- Order --------------------------------------------------------------------------------------------*/

	public function searchProductPriceIsDelivered($result)
	{
		return Order::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where(function ($query) {
   				 $query->orWhere('delivery_status', 'delivered');
   				 $query->orWhere('delivery_status', 'received');
  			 })
			->sum('total_amount');
	}

	public function searchProductPriceTotal($result)
	{
		return Order::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('total_amount');
	}

	public function searchGoldProductPriceIsDelivered($result)
	{
		return Order::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where(function ($query) {
   				 $query->orWhere('delivery_status', 'delivered');
   				 $query->orWhere('delivery_status', 'received');
  			 })
			->where(function ($query) {
   				 $query->orWhere('is_gold_bean', true);
   				 $query->orWhere('is_gold_coin', true);
  			 })
			->sum('total_amount');
	}

	public function searchGoldProductPrice($result)
	{
		return Order::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where(function ($query) {
   				 $query->orWhere('is_gold_bean', true);
   				 $query->orWhere('is_gold_coin', true);
  			 })
			->sum('total_amount');
	}

	public function searchProductCostIsDelivered($result)
	{
		return Order::query()
			->join('order_items','orders.id','=','order_items.order_id')
			->whereNotNull('orders.paid_at')
			->where('orders.created_at', '>', $result['start'])
			->where('orders.created_at', '<', $result['end'])
			->where(function ($query) {
   				 $query->orWhere('orders.delivery_status', 'delivered');
   				 $query->orWhere('orders.delivery_status', 'received');
  			 })
			->sum('order_items.total_cost');
	}

	public function searchProductCost($result)
	{
		return Order::query()
			->join('order_items','orders.id','=','order_items.order_id')
			->whereNotNull('orders.paid_at')
			->where('orders.created_at', '>', $result['start'])
			->where('orders.created_at', '<', $result['end'])
			->sum('order_items.total_cost');
	}

	public function searchOwnRewards($result)
	{
		return Reward::query()
			->where('remark','own_order')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	public function searchOneLevelRewards($result)
	{
		return Reward::query()
			->where('remark','first_level')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	public function searchTwoLevelRewards($result)
	{
		return Reward::query()
			->where('remark','second_level')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	public function searchPreMonthRewards()
	{
		return Reward::query()
			->where(function ($query) {
   				 $query->orWhere('remark', 'own_order');
   				 $query->orWhere('remark', 'first_level');
   				 $query->orWhere('remark', 'second_level');
  			 })
			->whereBetween('created_at', [Carbon::now()->subMonthNoOverflow()->startOfMonth(),  Carbon::now()->subMonthNoOverflow()->endOfMonth()])
			->sum('money');
	}

	public function searchMonthRewards()
	{
		return Reward::query()
			->where(function ($query) {
   				 $query->orWhere('remark', 'own_order');
   				 $query->orWhere('remark', 'first_level');
   				 $query->orWhere('remark', 'second_level');
  			 })
			->whereBetween('created_at', [Carbon::now()->startOfMonth(),  Carbon::now()->endOfMonth()])
			->sum('money');
	}



	/*---------- gold records ------------------------------------------------------------------------------------------*/

	public function searchGoldRecordsCount($result)
	{
		$mockedUser = User::where('is_mock',1)->pluck('id')->toArray();
		$mockUserId = !empty($mockedUser) ? $mockedUser : []; 
		$query = GoldRecord::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->whereNotIn('user_id', $mockUserId);

		if($result['gold_round_id']){
			$query = $query->where('gold_round_id', $result['gold_round_id'])->count();
		}else{
			$query = $query->count();
		}
		return $query;
	}

	public function searchGoldRecordsMoney($result)
	{
		$mockedUser = User::where('is_mock',1)->pluck('id')->toArray();
		$mockUserId = !empty($mockedUser) ? $mockedUser : []; 
		$query = GoldRecord::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->whereNotIn('user_id', $mockUserId);

		if($result['gold_round_id']){
			$query = $query->where('gold_round_id', $result['gold_round_id'])->sum('pay_net_price');
		}else{
			$query = $query->sum('pay_net_price');
		}

		return $query;
	}

	public function searchGoldRecordsMoneyRange($result)
	{
		$mockedUser = User::where('is_mock',1)->pluck('id')->toArray();
		$mockUserId = !empty($mockedUser) ? $mockedUser : []; 
		$mockUserId = implode($mockUserId,',');

		if($result['gold_round_id']){
			if($mockUserId){ 
				$sql = "select count(*) as count from (select sum(pay_net_price) as total from gold_records where created_at > '{$result['start']}' and created_at < '{$result['end']}' and user_id not in ({$mockUserId}) and gold_round_id = {$result['gold_round_id']} GROUP BY user_id) a where total > {$result['min']} and total <= {$result['max']}";
			}else{
				$sql = "select count(*) as count from (select sum(pay_net_price) as total from gold_records where created_at > '{$result['start']}' and created_at < '{$result['end']}'  and gold_round_id = {$result['gold_round_id']} GROUP BY user_id) a where total > {$result['min']} and total <= {$result['max']}";
			}
			
		}else{
			if($mockUserId){
				$sql = "select count(*) as count from (select sum(pay_net_price) as total from gold_records where created_at > '{$result['start']}' and created_at < '{$result['end']}' and user_id not in ({$mockUserId}) GROUP BY user_id) a where total > {$result['min']} and total <= {$result['max']}";
			}else{
				$sql = "select count(*) as count from (select sum(pay_net_price) as total from gold_records where created_at > '{$result['start']}' and created_at < '{$result['end']}'  GROUP BY user_id) a where total > {$result['min']} and total <= {$result['max']}";

			}	
			
		}
		
		$query = DB::select($sql, [1]);
		return $query[0]->count;
	}

	public  function searchGoldRecordsUserCount($result)
	{
		$mockedUser = User::where('is_mock',1)->pluck('id')->toArray();
		$mockUserId = !empty($mockedUser) ? $mockedUser : []; 
		
		$query = GoldRecord::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->whereNotIn('user_id', $mockUserId);

		if($result['gold_round_id']){
			$query = $query->where('gold_round_id', $result['gold_round_id'])->distinct('user_id')->count();
		}else{
			$query = $query->distinct('user_id')->count();
		}
		return $query;
	}

	public  function searchUserCandidateTotalAmount()
	{
		$mockedUser = User::where('is_mock',1)->pluck('id')->toArray();
		$mockUserId = !empty($mockedUser) ? $mockedUser : []; 
		$count = GoldRecord::query()
			->whereNotNull('paid_at')
			->whereNotIn('user_id', $mockUserId)
			->distinct('user_id')
			->count();

		return $count;
	}

	/*---------- gold coins ------------------------------------------------------------------------------------------*/

	public function searchGoldCoinsDirectPrize($result)
	{
		return GoldCoin::query()
			->where('remark', 'gold_record_direct_prize')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	public function searchGoldCoinsDirectSupportPrize($result)
	{
		return GoldCoin::query()
			->where('remark', 'support_prize_coins')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	public function searchGoldCoinsTeamSupportPrize($result)
	{
		return GoldCoin::query()
			->where('remark', 'support_team_coins')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}


	public function searchGoldCoinsTeamPrize($result)
	{
		return GoldCoin::query()
			->where('remark', 'gold_record_team_prize')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	public function searchGoldCoinsOverTime($result)
	{
		return GoldBean::query()
			->where('remark', 'overtime_fine_beans')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	public function searchSystemtransfer($result)
	{
		return GoldCoin::query()
			->where('remark', 'system_transfer_coins')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	public function searchSystemtRecall($result)
	{
		return GoldCoin::query()
			->where('remark', 'system_recall')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	

	public function searchGoldBeansPresent($result)
	{
		return GoldBean::query()
			->where('remark', 'order_gold_beans')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	/*---------- gold subsidies ------------------------------------------------------------------------------------------*/

	public function searchGoldSubsidiesUnlocked($result)
	{
		return GoldSubsidy::query()
			->where('remark', 'unlocked_gold_subsidies')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}
	
	/*---------- gold cashes ------------------------------------------------------------------------------------------*/
	public function searchGoldCashesBuy($result)
	{
		return GoldBean::query()
			->where('remark', 'buying_gold_cashes')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	public function searchGoldCashesFromBeans($result)
	{
		return GoldCash::query()
			->where('remark', 'gold_cashes_from_beans')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	public function searchGroundingProduct($result)
	{
		return GoldProduct::query()
			->where('started_at', '>', $result['start'])
			->where('started_at', '<', $result['end'])
			->count();
	}

	public function searchPendingSplit($result)
	{
		return GoldRecord::query()
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->whereNotNull('to_split_applied_at')
			->count();
	}



	public function searchYesterdayUser()
	{
		return User::query()
			->whereDate('created_at', Carbon::yesterday())
			->where('admin_user_id',Admin::user()->id)
			->count();
	}

	public function searchTodayUser()
	{
		return User::query()
			->whereDate('created_at', Carbon::today())
			->where('admin_user_id',Admin::user()->id)
			->count();
	}


	public function searchUserAll()
	{
		return User::query()
			->where('admin_user_id',Admin::user()->id)
			->count();
	}


	public function searchYesterdayValiUser()
	{
		$res =  GoldRecord::query()
			->join('users','gold_records.user_id','=','users.id')
			->whereDate('gold_records.created_at', Carbon::yesterday())
			->where('users.admin_user_id',Admin::user()->id)
			->groupBy('users.id')
			->get()->toArray();
		return count($res);
	}

	public function searchToValiUser()
	{
		$res =  GoldRecord::query()
			->join('users','gold_records.user_id','=','users.id')
			->whereDate('gold_records.created_at', Carbon::today())
			->where('users.admin_user_id',Admin::user()->id)
			->groupBy('users.id')
			->get()->toArray();
		return count($res);
	}

	public function searchValiUserAll()
	{
		return User::query()
			->where('admin_user_id',Admin::user()->id)
			->where('is_gold_intern',true)
			->count();
	}


	public function searchYesterdayChargeBeans()
	{
		return User::query()
			->join('gold_beans','gold_beans.user_id','=','users.id')
			->whereDate('gold_beans.created_at', Carbon::yesterday())
			->where('users.admin_user_id',Admin::user()->id)
			->where('gold_beans.remark','present_gold_beans')
			->sum('gold_beans.money');
	}

	public function searchTodayChargeBeans()
	{
		return User::query()
			->join('gold_beans','gold_beans.user_id','=','users.id')
			->whereDate('gold_beans.created_at', Carbon::today())
			->where('users.admin_user_id',Admin::user()->id)
			->where('gold_beans.remark','present_gold_beans')
			->sum('gold_beans.money');
	}

	public function searchChargeBeansAll()
	{
		return User::query()
			->join('gold_beans','gold_beans.user_id','=','users.id')
			->where('users.admin_user_id',Admin::user()->id)
			->where('gold_beans.remark','present_gold_beans')
			->sum('gold_beans.money');
	}
	
}
